Dataset Description¶
The dataset is derived from emergency department of a hospital and consists of following information:
- MRN - Unique identifier of a patient
- Presentation Visit Number - Unique identifier for a visit
- Triage Priority - Identifies the priority of the visit based on triage nurse assessment
- Age - Age of patient
- Arrival Date - Date time when arrived in hospital
- Dr Seen Date - Date time when doctor saw the patient
- Depart Actual Date - Date time when patient left the hospital
- Depart Status Code - Code for patient representing discharge status
- Depart Status Desc. - Description of code for patient representing discharge status
- Depart. Dest. Code. - Code representing department where patient is sent to
- Depart. Dest. Desc. - Description of department where patient is sent to
- TimeDiff Arrival - Actual Depart (mins) - Total treatment time of patient in the hospital
- TimeDiff TreatDrNr-Act.Depart (mins) - Total time spent by patient in emergency department after seeing a doctor.
- Presenting Complaint Code - Code for complaint made by the patient
- Presenting Complaint Desc. - Description of complaint made by the patient
- Diag Code - Code for diagnosis
- Diagnosis Desc. - Description of diagnosis
In [9]:
#Load libraries
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
from datetime import datetime
import numpy as np
Exploratory Data Analysis¶
This section of the notebook performs exploration of the dataset. Since the data represents functioning of hospital emergency department, questions have been grouped in three categories:
1. Factors affecting total time spent by patients in the hospital¶
- What is the distribution of total time spent by patients in the hospital?
- How is total treatment time dependent on the diagnosis?
- How is total treatment time distributed with age of patient?
- How is total treatment time dependent on the complaints mentioned by the patients?
- How is total treatment time distributed with triage nurse assessment of the patients in hospital?
- How is total treatment time distributed with time when doctor sees a patient in the hospital?
2. Number of patients coming for treatment¶
- What is the total number of patients recorded per day?
- Is the number of patients recorded per day dependent on month or year?
- Is there any trend, any seasonal pattern to the number of patients visiting per day?
3. Patient complaints and diagonsis¶
- What is the most common occuring diagnosis?
- How many patients have multiple visits?
- What is the most common occuring complaint from patients?
- What information does 'Departure Status Description' presents about the patient handling?
- What kind of complaints did patients reported who did not wait for diagnosis/discharge process to complete?
In [3]:
# Load Dataset
filename = 'Generic ED 2009.xlsx'
dataset = pd.read_excel(filename)
dataset.head()
Out[3]:
In [4]:
dataset.columns
Out[4]:
In [5]:
# Checking for null values
dataset.isnull().sum()
Out[5]:
In [49]:
dataset.dtypes
Out[49]:
In [63]:
from pandas_profiling import ProfileReport
ProfileReport(dataset, title="Pandas Profiling Report")
Out[63]:
Data Cleaning¶
It is seen there are some null values in a few columns. Following cells fill up null values as explained below:
- Presenting Complaint Desc. - Using the diagonsis description for these entries, null value is filled by the most common complaint desc for this diagnosis description.
- Presenting Complaint Code. - Using the diagonsis code for these entries, null value is filled by the most common complaint code for this diagnosis description.
- TimeDiff TreatDrNr-Act. Depart (mins) - Null value is filled by considering half of the TimeDiff Arrival-Actual Depart (mins). Since there is just one null entry, this approximate approach has been used. Other approaches like using median value for some diagnosis code can also be used.
- Dr Seen Date - Null values for this feature were not filled as information in TimeDiff TreatDrNr-Act. Depart (mins) column is present for these entries.
In [6]:
dataset.loc[dataset['TimeDiff TreatDrNr-Act. Depart (mins)'].isnull(),'TimeDiff TreatDrNr-Act. Depart (mins)'] = dataset.loc[dataset['TimeDiff TreatDrNr-Act. Depart (mins)'].isnull(),'TimeDiff Arrival-Actual Depart (mins)']/2
In [7]:
#check for most diagnosis desc
diag_desc = dataset[dataset['Presenting Complaint Code'].isnull()].loc[:,'Diagnosis Desc.'].unique().tolist()
diag_desc
Out[7]:
In [10]:
diag_desc = dataset[dataset['Presenting Complaint Code'].isnull()].loc[:,'Diagnosis Desc.'].unique().tolist()
#diag_desc
for diag in diag_desc:
df_red = dataset[dataset['Diagnosis Desc.']==diag].groupby(['Presenting Complaint Desc.','Presenting Complaint Code'])['MRN'].count().reset_index()
max_count = df_red.MRN.max()
#print(df_red[df_red.MRN==max_count].iloc[0,0])
val = df_red[df_red.MRN==max_count].iloc[0,0]
val1 = df_red[df_red.MRN==max_count].iloc[0,1]
dataset.loc[np.array(dataset['Presenting Complaint Code'].isnull()) & np.array(dataset['Diagnosis Desc.']==diag),'Presenting Complaint Desc.']= val
dataset.loc[np.array(dataset['Presenting Complaint Code'].isnull()) & np.array(dataset['Diagnosis Desc.']==diag),'Presenting Complaint Code']= val1
In [45]:
#Checking combination of diagnosis code and diagnosis description together
dataset.groupby(by=['Diag Code', 'Diagnosis Desc.'])['Diag Code'].count()
Out[45]:
In [46]:
# Correct spelling errors
dataset.loc[dataset['Diagnosis Desc.']=='FOOD POISOINING','Diagnosis Desc.']='FOOD POISONING'
In [ ]:
In [11]:
sns.distplot(dataset['TimeDiff Arrival-Actual Depart (mins)'])
Out[11]:
In [12]:
dataset['TimeDiff Arrival-Actual Depart (mins)'].describe()
Out[12]:
2. How is total treatment time dependent on the diagnosis?¶
- Using diagnosis description, total number of different diagnosis and there occurence frequency is first determined.
- This indicates the most commonly occurring illnesses treated by the emergency department.
- It is interesting to note that most frequent diagnosis description is 'DID NOT WAIT'. This needs further analysis to determine what complaints were reported by these patients.
- From the box plot it can be seen that distribution of total treatment time changes with diagnosis. All of these distributions are right skewed, indicating presence of extreme cases (outliers) for all illness who take more time in the hospital.
- As expected, total time spent in hospital is dependent on the diagnosis.
In [15]:
dataset['Diagnosis Desc.'].value_counts()
Out[15]:
In [17]:
plt.figure(figsize=(25,200))
sns.set(font_scale=1.5)
sns.boxplot(x="TimeDiff Arrival-Actual Depart (mins)",y='Diagnosis Desc.', data=dataset,orient="h")
Out[17]:
3. How is total treatment time distributed with age of patient?¶
- Dataset contains patients between age 0 to 15 years.
- It can be seen from the boxplot below that the distribution of total treatment time is similar for all age from 0 to 15 years.
- Total treatment time distribution for each age group is right skewed indicating presence of extreme cases that take longer to recover.
In [28]:
plt.figure(figsize=(15,8))
sns.boxplot(x = ' Age (yrs)', y= "TimeDiff Arrival-Actual Depart (mins)",data=dataset)
Out[28]:
4. How is total treatment time distributed with triage nurse assessment of the patients in hospital?¶
- It can be seen from the box plot below that the median value of total time distribution reduces as triage priority value increases. This indicates that immediate attention (shorter response time) is given to the the patients with higher triage priority.
In [29]:
plt.figure(figsize=(15,8))
sns.boxplot(x = 'Triage Priority', y= "TimeDiff Arrival-Actual Depart (mins)",data=dataset)
Out[29]:
5. How is total treatment time distributed with time when doctor sees a patient in the hospital?¶
- It can be seen from the scatter plot below that total treatment time in hospital and time when doctor attends to discharge time are highly correlated. This can also be seen in the correlation coefficient of 0.91.
In [36]:
plt.figure(figsize=(10,10))
sns.scatterplot(x = 'TimeDiff TreatDrNr-Act. Depart (mins)', y= "TimeDiff Arrival-Actual Depart (mins)",data=dataset)
Out[36]:
In [38]:
dataset.loc[:,['TimeDiff TreatDrNr-Act. Depart (mins)', "TimeDiff Arrival-Actual Depart (mins)"]].corr()
Out[38]:
6. How is total treatment time dependent on the complaints mentioned by the patients?¶
- First of all frequency of different kinds of complaints is calculated.
- Box plot below shows distribution of total treatment time varies with the complaints recorded by the patients. As expected, type of complaint reported will affect the total treatment time of the patient.
In [39]:
dataset['Presenting Complaint Desc.'].value_counts()
Out[39]:
In [40]:
plt.figure(figsize=(25,200))
sns.set(font_scale=1.5)
sns.boxplot(x="TimeDiff Arrival-Actual Depart (mins)",y='Presenting Complaint Desc.', data=dataset,orient="h")
Out[40]:
Number of patients coming for treatment¶
1. What is the total number of patients recorded per day?¶
- Number of patients recorded per day by the department is plotted as a time series plot.
2. Is the number of patients recorded per day dependent on month or year?¶
- It can be seen that the count of patients recorded per day is dependent on the month and year.
3. Is there any trend, any seasonal pattern to the number of patients visiting per day?¶
- It is seen from the plot that there is a cyclic component to the time series, indicating a pattern with respect to month/year in the number of patients that come to the hosiptal.
In [71]:
pat_date = dataset['Arrival Date'].apply(lambda x: x.strftime('%Y-%m-%d'))
In [72]:
pat_date.value_counts(sort=False).reset_index()
pat_date = pat_date.value_counts(sort=False).reset_index()
pat_date.columns = ['Arrival Date','patient_count']
pat_date['Arrival Date'] = pd.to_datetime(pat_date['Arrival Date'],format = '%Y-%m-%d')
pat_date.sort_values(by=['Arrival Date'], inplace=True)
fig = plt.figure(figsize=(20,10))
ax = fig.add_subplot(111)
plt.plot(pat_date['Arrival Date'],pat_date['patient_count'])
ax.set_xlabel('Date')
ax.set_ylabel('Number of Patients per day')
Out[72]:
NEXT STEPS - SETTING UP THE BUSINESS PROBLEM¶
Efficient running of emergency department needs planning of resources, availability of adequate hospital staff etc. This can be facilitated if an estimate is available on the total time a patient is expected to spend in the hospital after arrival. Exploratory Data Analysis for this dataset showed dependency of total treatment time on many factors like diagonsis, age of patient, complaint description etc.
In addition to above, planning of hospital operation can be further improved if total demand in terms of number of patients per day can be forecasted using historical data. It is seen that there is a periodic dependency in the number of patients per day with time of the year.
Using these insights, it is proposed to devise the following two pipelines :
Prediction of total treatment time of patients¶
- Total treatment time of a patient is predicted using information in features like diagnosis, nurse priority, compliant etc using XGBOOST - Regression
- Details and results are presented in jupyter notebook Hospital_Time_Prediction.ipynb
Forecasting of number of patients per day¶
- Using historical count of patients per day, total demand on hospital is forecasted with a horizon of next 60 days using Time Series Forecasting Techniques.
- Details and results are presented in jupyter notebook Patients_Count_Forecasting.ipynb
In [ ]:
In [44]:
dataset['Diagnosis Desc.'].value_counts()
Out[44]:
2. How many patients have multiple visits?¶
- It can be seen below that patients have mulitple visits. This gives an indication about recurring complaints or seasonal diseases.
In [47]:
dataset['MRN'].value_counts()
Out[47]:
3. What is the most common occuring complaint from patients?¶
In [48]:
dataset['Presenting Complaint Desc.'].value_counts()
Out[48]:
4. What information does 'Departure Status Description' presents about the patient handling?¶
- It can be seen below that majority of the patients (around 75%) are discharged after completing the treatment in emergency department.
- Around 11 % of patients are admitted to the hospital and about 10% of the patients did not wait for discharge process to complete. It will be interesting to analyse these patients to understand the complaints they had.
In [82]:
dataset['Departure Status Desc.'].value_counts()
Out[82]:
In [83]:
dataset['Depart Status Code'].value_counts()
Out[83]:
In [102]:
dataset['Depart. Dest. Desc.'].value_counts()
Out[102]:
5. What kind of complaints did patients reported who did not wait for diagnosis/discharge process to complete?¶
- Following plots present information on the age group and complaints of patients who did not wait for diagnosis. It is seen that patients belong to all age groups and different types of complaints.
In [57]:
df = dataset[dataset['Diagnosis Desc.']=='DID NOT WAIT']
In [61]:
df.head()
Out[61]:
In [62]:
sns.distplot(df['TimeDiff TreatDrNr-Act. Depart (mins)'])
Out[62]:
In [55]:
df['Presenting Complaint Desc.'].value_counts()
Out[55]:
In [48]:
df1=df['Presenting Complaint Desc.'].value_counts()
In [64]:
sns.distplot(df[' Age (yrs)'])
Out[64]:
In [57]:
plt.figure(figsize=(20,20))
sns.barplot(df1.values,df1.index)
Out[57]:
In [54]:
import sweetviz as sv
In [55]:
my_report = sv.analyze(dataset, target_feat='TimeDiff Arrival-Actual Depart (mins)')
my_report.show_html() # Default arguments will generate to "SWEETVIZ_REPORT.html"
In [ ]:
In [ ]:
In [ ]: